package com.CMUBiS.Depreciated;

import java.util.*;
import java.util.Date;
import java.sql.*;
import java.text.*;
import org.apache.cassandra.cql.jdbc.*;

import com.CMUBiS.DB.DBQuery;
import com.CMUBiS.util.Item;

/**
 * 
 * @author John Payne
 * @version 0.1
 * @since 0.1
 *
 */
/*
public class CassandraDBQuery extends DBQuery{
	protected String url = null;
	protected String driver = null;
	protected String username = null; 
	protected String password = null;
	protected boolean useConnectionPool = false;
	
	public CassandraDBQuery(){
		super();
		
		this.init();
	}
	
	// This should be read from a file
	protected void init(){
		this.url = "jdbc:cassandra:10.22.0.102@9160";
		this.driver = "org.apache.cassandra.cql.jdbc.CassandraDriver";
		this.username = "root";
		this.password = "testtest";
	}
	
	protected Connection initConnection(){
		try {
			Class.forName(driver).newInstance();
			Connection conn = DriverManager.getConnection(url,username,password);
			return conn;
		}
		catch(Exception e){
			System.err.println("MySQLQuery (initConnection): Could not open a new database connection to " + this.url);
			e.printStackTrace();
			return null;
		}
	}
	
	protected Connection getConnection(){
		if(!this.useConnectionPool){
			return initConnection();
		}
		else{
			return null;
		}
	}
	
	protected void closeConnection(Connection conn){
		if(conn != null){
			if(!this.useConnectionPool){
				// close the connection
				try{
					conn.close();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (closeConnection): Could not close database connection");
					e.printStackTrace();
				}	
			}
			else{
				// re-pool the connection
				
			}
		}
	}
	
	public int checkUsernamePassword(String username, String password){
		Connection conn = this.getConnection();
		int userID = -1;
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the username and password are correct
				PreparedStatement statement = conn.prepareStatement("SELECT `id` FROM `users` FORCE INDEX (`usernamePasswordIndex`) WHERE `users`.`username` = ? AND `users`.`password` = ?");
				statement.setString(1, username);
				statement.setString(2, password);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, set correct to true
				if(rs.next()){
					userID = rs.getInt("id");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (correctUsernamePassword): Could not check username and password");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return userID;
	}
	
	public String makeNewAuthToken(int userID){
		// Get the new auth token
		String newAuthToken = authToken(userID);
		String authToken = null;
		
		if(newAuthToken != null){
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to update the auth token
					PreparedStatement statement = conn.prepareStatement("UPDATE `users` SET `users`.`authToken` = ? WHERE `users`.`id` = ?");
					statement.setString(1,newAuthToken);
					statement.setInt(2, userID);
					
					int success = statement.executeUpdate();
					
					// We we did update the row, then we want to return the new auth token
					if(success==1) authToken = newAuthToken;
					
					statement.close();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (makeNewAuthToken): Could not insert the auth token");
					e.printStackTrace();
				}
				
				this.closeConnection(conn);
			}
		}
		
		return authToken;
	}
	
	public boolean checkAuthToken(int userID, String authToken){
		Connection conn = this.getConnection();
		boolean correct = false;
		
		if(conn != null){
			try{
				// Create the SQL statement to get the auth token
				PreparedStatement statement = conn.prepareStatement("SELECT `authToken` FROM `users` FORCE INDEX (`idIndex`) WHERE `users`.`id` = ?");
				statement.setInt(1, userID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has an end date
				if(rs.next()){
					String resultAuth = rs.getString("authToken");
					if(resultAuth.equals(authToken)) correct = true;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (checkAuthToken): Could not read the auth toekn");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return correct;
	}
	
	public Date getItemEndDate(int itemID){
		Connection conn = this.getConnection();
		Date endDate = null;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's end date
				PreparedStatement statement = conn.prepareStatement("SELECT `endDate` FROM `items` FORCE INDEX (`idIndex`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has an end date
				if(rs.next()){
					endDate = this.sdf.parse(rs.getString("endDate"));
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getItemEndTime): Could not get the item end time");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return endDate;
	}
	
	public float getCurrentBid(int itemID){
		Connection conn = this.getConnection();
		float currentBid = Float.MAX_VALUE;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's maxBid
				PreparedStatement statement = conn.prepareStatement("SELECT `currentBid` FROM `items` FORCE INDEX (`idIndex`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has a maxBid
				if(rs.next()){
					currentBid = rs.getFloat("currentBid");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getCurrentBid): Could not get the item's current bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return currentBid;
	}
	
	//TODO: maybe we should read this and current together since they'll both be checked
	public float getMaxBid(int itemID){
		Connection conn = this.getConnection();
		float maxBid = Float.MAX_VALUE;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's maxBid
				PreparedStatement statement = conn.prepareStatement("SELECT `maxBid` FROM `items` FORCE INDEX (`idIndex`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has a maxBid
				if(rs.next()){
					maxBid = rs.getFloat("maxBid");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getMaxBid): Could not get the item's max bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return maxBid;
	}
	
	public int getQuantity(int itemID){
		Connection conn = this.getConnection();
		int quantity = Integer.MIN_VALUE;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's quantity
				PreparedStatement statement = conn.prepareStatement("SELECT `quantity` FROM `items` FORCE INDEX (`idIndex`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has a quantity
				if(rs.next()){
					quantity = rs.getInt("quantity");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getQuantity): Could not get the item's quantity");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return quantity;
	}
	
	public Item getItem(int itemID){
		Connection conn = this.getConnection();
		Item result = null;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's details
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `items` FORCE INDEX (`idIndex`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If an item is returned then get the details
				if(rs.next()){
					result = new Item(rs.getInt("items.id"),
							rs.getString("items.name"),
							rs.getString("items.description"),
							rs.getInt("items.quantity"),
							rs.getFloat("items.startPrice"),
							rs.getFloat("items.reservePrice"),
							rs.getFloat("items.buyNowPrice"),
							rs.getFloat("items.currentBid"),
							rs.getFloat("items.maxBid"),
							rs.getInt("items.noOfBids"),
							this.sdf.parse(rs.getString("items.startDate")),
							this.sdf.parse(rs.getString("items.endDate")),
							rs.getInt("items.sellerID"),
							rs.getInt("items.categoryID"),
							new ArrayList<Image>());
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getItem): Could not get the item");
				e.printStackTrace();
				result = null;
			}
			
			this.closeConnection(conn);
		}
		
		return result;
	}
	
	public String getFirstName(int userID){
		Connection conn = this.getConnection();
		String name = null;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's details
				PreparedStatement statement = conn.prepareStatement("SELECT firstname FROM `users` FORCE INDEX (`idIndex`) WHERE `users`.`id` = ?");
				statement.setInt(1, userID);
				
				ResultSet rs = statement.executeQuery();
				
				// If an item is returned then get the details
				if(rs.next()){
					name = rs.getString("firstname");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getItem): Could not get the item");
				e.printStackTrace();
				name = null;
			}
			
			this.closeConnection(conn);
		}
		
		return name;
	}
	
	public ArrayList<Image> getItemImages(int itemID){
		Connection conn = this.getConnection();
		ArrayList<Image> images = new ArrayList<Image>();

		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT `imageID`,`position` FROM `item_image` FORCE INDEX (imageIDIndex) WHERE `item_image`.`itemID` = ? ORDER BY `position` ASC");
				statement.setInt(1, itemID);
				
				//TODO: shouldn't I just join these? - Andy
				
				ResultSet rs = statement.executeQuery();
				
				PreparedStatement getImagesStatement = conn.prepareStatement("SELECT `url`,`description` FROM `images` WHERE `images`.`id` = ?");
				
				// If image IDs are returned, we need to read the image URLs
				while(rs.next()){
					int imageID = rs.getInt("imageID");
					
					getImagesStatement.setInt(1, imageID);
					
					ResultSet rs2 = getImagesStatement.executeQuery();
					
					// If the image really exists, then we will add the details to the images array
					if(rs2.next()){
						Image newImg = new Image(rs.getInt("position"), rs2.getString("url"), rs2.getString("description"));
						images.add(newImg);
					}
					
					rs2.close();
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getItem): Could not get the item");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return images;
	}
	
	public ArrayList<Bid> getCurrentBids(int userID){
		return this.getXBids(userID, "bids");
	}
	
	public ArrayList<Bid> getOldBids(int userID){
		return this.getXBids(userID, "oldBids");
	}
	
	public boolean buyItem(int userID, int itemID){
		boolean bought = false;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				// We will move the item to the oldItems table if we buy it
				// If the row move is successful then we know we are the one how bought it
				//TODO: make sure quantity is correct
				//TODO: make sure this works for concurrent
				
				//TODO: check the price is correct
				
				// Move the item
				//TODO: use index
				//TODO: with quantities we may need to lock?
				PreparedStatement buyItemStatement = conn.prepareStatement("INSERT INTO `oldItems` SELECT * FROM `items` WHERE `items`.`id` = ?");
				buyItemStatement.setInt(1, itemID);
				
				//TODO: make sure it worked
				buyItemStatement.executeUpdate();
				buyItemStatement.close();
				
				PreparedStatement removeItemStatement = conn.prepareStatement("DELETE FROM `items` WHERE `items`.`id` = ?");
				removeItemStatement.setInt(1, itemID);
				
				removeItemStatement.executeUpdate();
				removeItemStatement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (buyItem): Could not get insert bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return bought;
	}
	
	public void insertBid(int userID, int itemID, int quantity, float bid, float maxBid){
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				/*
				 * 1. For consistent budding we need to make sure no one enters a lower-maxBid when we are writing
				 * 2. So we must stop people updating the item or entering a bid while we are updating the item
				 * 3. Whoever is winning the bidding now must have the current largest 'maxBid'
				 * 4. The items new price will be the smallest of the 'maxBid' prices plus one cent, or the bid value
				 * 		- unless both maxBids are the same, then the bid will be a draw and the price will be maxBid
				 * 		- (who wins will be sorted out once the auction is over, we are just doing the price here)
				 * 		- (we know that the bid is higher than the currentBid or this method should not have been called)
				 * 5. We'll also update the number of bids value no matter what happens
				 * 6. Lastly, just incase there was some out of order update, we'll make sure the new bids are greater than the old ones
				 
				
				// SQL to lock the items table
				PreparedStatement lockTableStatement = conn.prepareStatement("LOCK TABLES bids WRITE, items WRITE");
				
				// Get the item's currentBid and maxBid values (and noOfBids)
				PreparedStatement getBidsStatement = conn.prepareStatement("SELECT currentBid, maxBid, noOfBids FROM `items` FORCE INDEX (`PRIMARY`)" +
						"WHERE `items`.`id` = ?");
				getBidsStatement.setInt(1, itemID);
				
				// SQL to update the item with the new bids
				PreparedStatement updateItemStatement = conn.prepareStatement("UPDATE `items` SET `items`.`currentBid` = ?, `items`.`maxBid` = ?, `items`.`noOfBids` = ? WHERE `items`.`id` = ?");
				updateItemStatement.setInt(4,itemID);
				
				// Unlock the tables
				PreparedStatement unlockTableStatement = conn.prepareStatement("UNLOCK TABLES");
				
				// SQL to enter the user's bid
				PreparedStatement insertBidStatement = conn.prepareStatement("INSERT INTO `bids` (userID, itemID, quantity, bid, maxBid, bidDate) "
						+ "VALUES (?, ?, ?, ?, ?, NOW())");
				insertBidStatement.setInt(1, userID);
				insertBidStatement.setInt(2, itemID);
				insertBidStatement.setInt(3, quantity);
				insertBidStatement.setFloat(4, bid);
				insertBidStatement.setFloat(5, maxBid);
				
				// No need to sync as we are locking the tables. The sync would only be local to this instance
				// It would not work to just do a Java sync since we may have multiple instances 
				float newCurrentBid;
				float newMaxBid;
				
				//TODO: I feel like we may still get a sync issue here? maybe not if they'll all wait for the lock...
				lockTableStatement.executeUpdate();
				
				ResultSet rs = getBidsStatement.executeQuery();	
				
				if(rs.next()){
					float dbCurrentBid = rs.getFloat("currentBid");
					float dbMaxBid = rs.getFloat("maxBid");
					int dbNoOfBids = rs.getInt("noOfBids");
					
					if(dbMaxBid==maxBid){
						System.out.println("no 1");
						newCurrentBid = maxBid;
						newMaxBid = maxBid;
					}
					else if(dbMaxBid>maxBid){
						System.out.println("no 2");
						newCurrentBid = maxBid + 0.01f;
						newMaxBid = dbMaxBid;
					}
					else{
						System.out.println("no 3");
						newCurrentBid = (bid>dbMaxBid) ? bid : new Float(dbMaxBid + 0.01f);
						newMaxBid = maxBid;
						System.out.println(maxBid);
						System.out.println(maxBid+100f);
						System.out.println(0f);
						System.out.println(100f);
					}
					
					// TODO: check this. maybe do it better. I hate concurrency!
					// These should not be needed as long as there are no out of order bids
					if(dbCurrentBid>newCurrentBid) newCurrentBid = dbCurrentBid;
					if(dbMaxBid>newMaxBid) newMaxBid = dbMaxBid;
					
					updateItemStatement.setFloat(1,newCurrentBid);
					updateItemStatement.setFloat(2,newMaxBid);
					updateItemStatement.setInt(3,dbNoOfBids+1);
					
					System.out.println("UPdateing item");
					System.out.println("sent: " + maxBid + " " + bid);
					System.out.println("sent: " + dbMaxBid + " " + dbCurrentBid);
					System.out.println("sent: " + newMaxBid + " " + newCurrentBid);
					System.out.println(updateItemStatement.toString());
					
					updateItemStatement.executeUpdate();
				}
				unlockTableStatement.executeUpdate();
				unlockTableStatement.close();
				
				lockTableStatement.close();
				getBidsStatement.close();
				
				insertBidStatement.executeUpdate();
				insertBidStatement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (insertBid): Could not get insert bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
	}
	
	public ArrayList<Item> getCurrentSellingItems(int userID){
		return this.getXSellingItems(userID, "items");
	}
	
	public ArrayList<Item> getOldSellingItems(int userID){
		return this.getXSellingItems(userID, "oldItems");
	}
	
	protected ArrayList<Item> getXSellingItems(int userID, String table){
		ArrayList<Item> items = new ArrayList<Item>();
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `" + table + "` FORCE INDEX (`userIDIndex`)" +
						"WHERE `" + table + "`.`sellerID` = ? ORDER BY `" + table + "`.`endDate` ASC");
				statement.setInt(1, userID);
				
				ResultSet rs = statement.executeQuery();
				
				while(rs.next()){
					Item currentItem = new Item(rs.getInt("items.id"),
							rs.getString("items.name"),
							rs.getString("items.description"),
							rs.getInt("items.quantity"),
							rs.getFloat("items.startPrice"),
							rs.getFloat("items.reservePrice"),
							rs.getFloat("items.buyNowPrice"),
							rs.getFloat("items.currentBid"),
							rs.getFloat("items.maxBid"),
							rs.getInt("items.noOfBids"),
							this.sdf.parse(rs.getString("items.startDate")),
							this.sdf.parse(rs.getString("items.endDate")),
							rs.getInt("items.sellerID"),
							rs.getInt("items.categoryID"),
							new ArrayList<Image>());
					items.add(currentItem);
				}
				
				//if(items.isEmpty()) items = null;
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getXSellingItems): Could not get the items");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return items;
	}
	
	public ArrayList<Item> getCategoryItems(int categoryID){
		ArrayList<Item> items = new ArrayList<Item>();
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `items` FORCE INDEX (`categoryIDIndex`)" +
						"WHERE `items`.`categoryID` = ? ORDER BY `items`.`endDate` ASC");
				statement.setInt(1, categoryID);
				
				ResultSet rs = statement.executeQuery();
				
				while(rs.next()){
					Item currentItem = new Item(rs.getInt("items.id"),
							rs.getString("items.name"),
							rs.getString("items.description"),
							rs.getInt("items.quantity"),
							rs.getFloat("items.startPrice"),
							rs.getFloat("items.reservePrice"),
							rs.getFloat("items.buyNowPrice"),
							rs.getFloat("items.currentBid"),
							rs.getFloat("items.maxBid"),
							rs.getInt("items.noOfBids"),
							this.sdf.parse(rs.getString("items.startDate")),
							this.sdf.parse(rs.getString("items.endDate")),
							rs.getInt("items.sellerID"),
							rs.getInt("items.categoryID"),
							new ArrayList<Image>());
					items.add(currentItem);
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getCategoryItems): Could not get the items");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return items;
	}
	
	
	// May return some expired bids if they have not been moved to 'oldBids'
	private ArrayList<Bid> getXBids(int userID, String table){
		ArrayList<Bid> bids = new ArrayList<Bid>();
		Connection conn = this.getConnection();

		if(conn != null){
			try{
				//TODO: maybe read the items separate? what if the user bids for the same item multiple times?
				
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `"+table+"` FORCE INDEX (`userIDIndex`) " +
						"LEFT OUTER JOIN `items` on `"+table+"`.`itemID` = `items`.`id` " +
						"WHERE `"+table+"`.`userID` = ? ORDER BY `items`.`endDate` ASC");
				statement.setInt(1, userID);
				
				ResultSet rs = statement.executeQuery();
				
				while(rs.next()){
					Item currentItem = new Item(rs.getInt("items.id"),
							rs.getString("items.name"),
							rs.getString("items.description"),
							rs.getInt("items.quantity"),
							rs.getFloat("items.startPrice"),
							rs.getFloat("items.reservePrice"),
							rs.getFloat("items.buyNowPrice"),
							rs.getFloat("items.currentBid"),
							rs.getFloat("items.maxBid"),
							rs.getInt("items.noOfBids"),
							this.sdf.parse(rs.getString("items.startDate")),
							this.sdf.parse(rs.getString("items.endDate")),
							rs.getInt("items.sellerID"),
							rs.getInt("items.categoryID"),
							new ArrayList<Image>());
					
					//TODO: increase Bid to have all field or don't select them
					
					Bid currentBid = new Bid(rs.getInt(table+".quantity"), rs.getFloat(table+".bid"), rs.getFloat(table+".maxBid"), this.sdf.parse(rs.getString(table+".bidDate")),currentItem);
					bids.add(currentBid);
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getXBids): Could not get the bids from " + table);
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return bids;
	}
	
	//TODO: make create items methods?
	
	public ArrayList<Category> getCategories(int parent){
		ArrayList<Category> categories = new ArrayList<Category>();
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `categories` WHERE parent = ?");
				statement.setInt(1, parent);
				ResultSet rs = statement.executeQuery();
				
				// Put all of the states in to the results vector
				while(rs.next()){
					Category category = new Category(rs.getInt("categories.id"),
							rs.getString("categories.name"),
							rs.getInt("categories.parent"));
					
					categories.add(category);
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getCategories): Could not get the categories");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return categories;
	}
	
	public Category getCategory(int categoryID){
		Category category = null;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `categories` WHERE id = ?");
				statement.setInt(1, categoryID);
				ResultSet rs = statement.executeQuery();
				
				// Put all of the states in to the results vector
				if(rs.next()){
					category = new Category(rs.getInt("categories.id"),
							rs.getString("categories.name"),
							rs.getInt("categories.parent"));
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getCategory): Could not get the category");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return category;
	}
	
	public boolean insertUser(String username, String password, String email, String firstName, String lastName, String street, String town, int zip, int state){
		Connection conn = this.getConnection();
		boolean insertedUser = false;
		
		//TODO: fail if nulls passed
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("INSERT INTO `users` (firstname, lastname, username, password, email) "
										+ "VALUES (?, ?, ?, ?, ?)");
				statement.setString(1, firstName);
				statement.setString(2, lastName);
				statement.setString(3, username);
				statement.setString(4, password);
				statement.setString(5, email);
				
				statement.executeUpdate();
				statement.close();
				
				// TODO: insert other info such as address etc
				
				insertedUser = true;
			}
			catch(Exception e){
				// This could happen if two people happen to try and insert the same username at the same time
				// we could sync this method, but it is unlikely. And beside, the second users registration
				// will fail anyway no matter what we do
				System.err.println("MySQLQuery (insertUser): Could not insert user");
				e.printStackTrace();
				
				//TODO: remove bad info
				
				insertedUser = false;
			}
			
			this.closeConnection(conn);
		}
		
		return insertedUser;
	}
	
	
	public boolean emailExists(String email){
		Connection conn = this.getConnection();
		boolean exists = false;
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT `email` FROM `users` WHERE `users`.`email` = ?");
				statement.setString(1, email);
				
				ResultSet rs = statement.executeQuery();
				
				// If the e-mail address does exist in the table
				if(rs.next()){
					exists = true;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (checkEmailExists): Could not read results set");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return exists;
	}
	
	public boolean usernameExists(String username){
		Connection conn = this.getConnection();
		boolean exists = false;
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the username address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT `username` FROM `users` WHERE `users`.`username` = ?");
				statement.setString(1, username);
				
				ResultSet rs = statement.executeQuery();
				
				// If the username does exist in the table
				if(rs.next()){
					exists = true;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (checkUsernameExists): Could not read results set");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return exists;
	}
	
	public ArrayList<String[]> getStates(){
		ArrayList<String[]> states = new ArrayList<String[]>();
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `states`");
				ResultSet rs = statement.executeQuery();
				
				// Put all of the states in to the results vector
				while(rs.next()){
					String[] result = new String[3];
					result[0] = rs.getString("id");
					result[1] = rs.getString("short");
					result[2] = rs.getString("long");
					
					states.add(result);
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getStates): Could not read results set");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return states;
	}

	@Override
	protected void closeConnection(java.sql.Connection conn) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Date getItemEndDate(int itemID) {
		// TODO Auto-generated method stub
		return null;
	}
	
}*/
